package server.repositories;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import entities.Account;

public class AccountRepository
{
	private String dbLocation;
	private String dbUsername;
	private String dbPassword;
	
	public AccountRepository(String dbLocation, String dbUsername, String dbPassword)
	{
		this.dbLocation = dbLocation;
		this.dbUsername = dbUsername;
		this.dbPassword = dbPassword;
	}
	public boolean accountExists(String username)
	{
		boolean exists = false;
		
		java.sql.Connection dbConnection = null;
		
		try
		{
			dbConnection = this.getDatabaseConnection();
			Statement statement = dbConnection.createStatement();
			ResultSet results;
			
			statement.execute("SELECT userId FROM `users` WHERE username='" + username + "'");
			
			results = statement.getResultSet();
			
			exists = results.first();
		}
		catch (SQLException e) {}
		
		try
		{
			dbConnection.close();
		}
		catch (SQLException e) {}
			
		return exists;
	}
	public boolean accountExists(long userId)
	{
		boolean exists = false;
		
		java.sql.Connection dbConnection = null;
		
		try
		{
			dbConnection = this.getDatabaseConnection();
			Statement statement = dbConnection.createStatement();
			ResultSet results;
			
			statement.execute("SELECT userId FROM `users` WHERE userId='" + userId + "'");
			
			results = statement.getResultSet();
			
			exists = results.first();
		}
		catch (SQLException e) {}
		
		try
		{
			dbConnection.close();
		}
		catch (SQLException e) {}
			
		return exists;
	}
	public java.sql.Connection getDatabaseConnection() throws SQLException
	{
		return DriverManager.getConnection(dbLocation, dbUsername, dbPassword);
	}
	private Account getAccountFromResults(ResultSet results) throws SQLException
	{
		final int userIdIndex = 1;
		final int usernameIndex = 2;
		final int passwordIndex = 3;
		final int lastModifiedIndex = 4;
		final int creationTimeIndex = 5;
		Account account = null;
		
		if (results.getRow() == 0)
			results.next();
		
		if (results.getRow() > 0)
		{
			account = new Account();
			
			account.userId = results.getLong(userIdIndex);
			account.username = results.getString(usernameIndex);
			account.password = results.getString(passwordIndex);
			account.lastModified = results.getLong(lastModifiedIndex);
			account.creationTime = results.getLong(creationTimeIndex);
		}
		
		return account;
	}
	private void insertAccount(Account account)
	{
		try
		{
			java.sql.Connection dbConnection = this.getDatabaseConnection();
			Statement statement = dbConnection.createStatement();
			
			if (account.userId > 0 && this.accountExists(account.userId))
			{
				statement.execute("UPDATE capstone.users SET userId = userId+1 WHERE creationTime>" + account.creationTime + " ORDER BY userId DESC");
				statement.execute("SELECT userId FROM capstone.users WHERE creationTime>" + account.creationTime + " LIMIT 1");
				
				ResultSet results = statement.getResultSet();
				
				results.first();
				
				account.userId = results.getInt(1);
				
				statement.execute("INSERT INTO users (userId, username, password) VALUES ('" + account.userId + "', '" + account.username + "', '" + account.password + "')");
			}
			else
			{
				statement.execute("INSERT INTO users (username, password) VALUES ('" + account.username + "', '" + account.password + "')");
			}
		}
		catch (SQLException e) {}
	}
	public void persist(Account account)
	{
		if (this.accountExists(account.username))
		{
			updateAccount(account);
		}
		else
		{
			insertAccount(account);
		}
	}
	private void updateAccount(Account account)
	{
		java.sql.Connection dbConnection;
		try
		{
			dbConnection = this.getDatabaseConnection();
		
			Statement statement = dbConnection.createStatement();
			
			if (account.userId > 0)
				statement.execute("UPDATE users SET userId='" + account.userId + "' WHERE username='" + account.username + "'");
			
			statement.execute("UPDATE users SET password='" + account.password + "' WHERE username='" + account.username + "'");
		}
		catch (SQLException e){}
	}
	public Account get(String username)
	{
		Account account = null;
		java.sql.Connection dbConnection = null;
		
		try
		{
			dbConnection = this.getDatabaseConnection();
			Statement statement = dbConnection.createStatement();
			ResultSet results;
			
			statement.execute("SELECT * FROM `users` WHERE username='" + username + "'");
			
			results = statement.getResultSet();
			
			account = getAccountFromResults(results);
		}
		catch (SQLException e) {}
		
		if (dbConnection != null)
			try
			{
				dbConnection.close();
			}
			catch (SQLException e) {}
		
		return account;
	}
	private ArrayList<Account> getAccounts(String whereClause)
	{
		ArrayList<Account> accounts = new ArrayList<Account>();
		java.sql.Connection dbConnection = null;
		ResultSet results = null;
		
		try
		{
			dbConnection = this.getDatabaseConnection();
			
			Statement statement = dbConnection.createStatement();
			
			statement.execute("SELECT * FROM `users` WHERE " + whereClause);
			results = statement.getResultSet();
		}
		catch (SQLException e1) {}
		
		if (results != null)
		{
			try
			{
				while (results.next())
				{
					accounts.add(this.getAccountFromResults(results));
				}
			}
			catch (SQLException e) { e.printStackTrace(); }
		}
		
		if (dbConnection != null)
			try
			{
				dbConnection.close();
			}
			catch (SQLException e) {}
		
		return accounts;
	}
	public ArrayList<Account> getUpdatedAfter(long timestamp)
	{
		return getAccounts("lastModified>" + timestamp + " AND lastModified != creationTime");
	}
	public ArrayList<Account> getInsertedAfter(long timestamp)
	{
		return getAccounts("creationTime>" + timestamp);
	}
}
